package ict.kth.se.model.repository.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import ict.kth.se.exceptions.EntityAlreadyExistsInDBException;
import ict.kth.se.exceptions.EntityDoesntExistInDBException;
import ict.kth.se.model.Competence;
import ict.kth.se.model.repository.CompetenceRepository;

public class JdbcCompetenceRepository implements CompetenceRepository {

	private JdbcTemplate jdbcTemplate;
	private RowMapper<Competence> rowMapper = new CompetenceRowMapper();
	
	/**
	 * public constuctor
	 * @param dataSource datasource to set jdbctemplate
	 */
	public JdbcCompetenceRepository(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	/**
	 * Get Competence by id
	 * @param id Id of competence
	 * @return Found competence
	 */
	@Override
	public Competence getCompetenceById(Long id) throws SQLException{
		try{
			String sql = "SELECT * FROM Competence WHERE competence_id = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, id);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get Competence by name
	 * @param name_se Name of competence on swedish
	 * @param name_en Name of competence on english
	 * @return Found competence
	 */
	@Override
	public Competence getCompetenceByNames(String name_se, String name_en) throws SQLException{
		try{
			String sql = "SELECT * FROM Competence WHERE name_se = ? AND name_en = ?;";
			return jdbcTemplate.queryForObject(sql, rowMapper, name_se, name_en);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * Get all competence
	 * @return Found competences
	 */
	@Override
	public List<Competence> getAllCompetences() throws SQLException{
		try{
			String sql = "SELECT * FROM Competence;";
			return jdbcTemplate.query(sql, rowMapper);
		}
		catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	/**
	 * This method removes one competence from database (by it's ID)
	 * @param competence The competence to be removed
	 * @throws EntityDoesntExistInDBException If competence doesn't exist in database
	 * @throws SQLException
	 */
	@Override
	public void removeCompetence(Competence competence) throws EntityDoesntExistInDBException, SQLException{
		
		if(this.getCompetenceById(competence.getId()) == null)
			throw new EntityDoesntExistInDBException("No such competence in database");
		
		String sql = "DELETE FROM Competence WHERE competence_id = ?;";
		jdbcTemplate.update(sql, competence.getId());
	}
	
	/**
	 * This method adds an competence in DB
	 * @param competence Competence to be added
	 * @return Newly created competence
	 * @throws EntityAlreadyExistsInDBException If such competence already exists in DB (by name)
	 * @throws SQLException
	 */
	@Override
	public Competence createCompetence(Competence competence) throws EntityAlreadyExistsInDBException, SQLException{
		
		//Some control before insertion
		if(this.getCompetenceByNames(competence.getNameSe(), competence.getNameEn()) != null)
			throw new EntityAlreadyExistsInDBException("There is already such competence in DB.");
		
		//Keyholder to get new primary key
		KeyHolder keyHolder = new GeneratedKeyHolder();
		//Now we have to define all variables as final to be used in prepared statement
		final String name_se = competence.getNameSe();
		final String name_en = competence.getNameEn();
		
		final String sql = "INSERT INTO Competence (name_se, name_en) VALUES (?, ?);";
		
		//Now do insert query with returning new key
		jdbcTemplate.update(
			    new PreparedStatementCreator() {
			        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"competence_id"});
			            ps.setString(1, name_se);
			            ps.setString(2, name_en);
			            return ps;
			        }
			    },
			    keyHolder);
		
		//After update add new id and return
		competence.setId(keyHolder.getKey().longValue());
		return competence;
	}
	
	/**
	 * This method updates an competence in DB
	 * @param competence Competence to be updated
	 * @throws EntityDoesntExistInDBException If such competence doesn't exist in DB (by id)
	 * @throws SQLException
	 */
	@Override
	public void updateCompetence(Competence competence) throws EntityDoesntExistInDBException, SQLException{
		
		//Some controll before updating
		if(this.getCompetenceById(competence.getId()) == null)
			throw new EntityDoesntExistInDBException("No such competence in database");
		
		String sql = "UPDATE Competence SET name_se = ?, name_en = ? WHERE competence_id = ?;";
		
		jdbcTemplate.update(sql, competence.getNameSe(), competence.getNameEn(), competence.getId());
	}
	
	/**
	 * RowMapper to map competence objects 
	 */
	class CompetenceRowMapper implements RowMapper<Competence>{
		public Competence mapRow(ResultSet rs, int rowNum) throws SQLException{
			return mapCompetence(rs);
		}
	}
	
	/**
	 * Maps a row returned from a query of Competence to a Competence object.
	 * @param rs the result set with its cursor positioned at the current row
	 */
	private Competence mapCompetence(ResultSet rs) throws SQLException {
		Competence competence = new Competence();
		competence.setId(rs.getLong("competence_id"));
		competence.setNameSe(rs.getString("name_se"));
		competence.setNameEn(rs.getString("name_en"));
		return competence;
	}

}
